package com.study.easyexcel.business.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.study.easyexcel.api.ExcelUtils;
import com.study.easyexcel.api.annotation.ExcelExportIgnore;
import com.study.easyexcel.api.converter.CustomConverter;
import com.study.easyexcel.business.configure.ImgConfig;
import com.study.easyexcel.business.dao.WorkDao;
import com.study.easyexcel.business.entity.Legion;
import com.study.easyexcel.business.entity.Traveler;
import com.study.easyexcel.business.entity.TravelerQuery;
import com.study.easyexcel.business.listener.NoModelImportListener;
import com.study.easyexcel.business.listener.SimpleImportListener;
import com.study.easyexcel.business.service.WorkService;
import com.study.easyexcel.business.template.DefaultFlatGroupStyle;
import com.study.easyexcel.business.template.SpecialGroupTemplate;
import com.study.easyexcel.business.template.TravelerAssetTemplate;
import com.study.easyexcel.common.constant.ExcelConstants;
import com.study.easyexcel.common.constant.WeatherKey;
import com.study.easyexcel.common.domain.*;
import com.study.easyexcel.common.exception.CheckedException;
import com.study.easyexcel.common.exception.ExcelImportException;
import com.study.easyexcel.common.exception.InvalidURL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.MessageFormat;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.GZIPInputStream;

/**
 * @author yuhuan
 * @date 2023/5/8
 */
@Service
public class WorkServiceImpl implements WorkService {

    private static final Logger logger = LoggerFactory.getLogger(WorkServiceImpl.class);

    @Resource
    private WorkDao workDao;

    @Resource
    private TransactionTemplate transactionTemplate;

    @Resource
    private ObjectMapper objectMapper;

    @Resource
    private ImgConfig imgConfig;

    @Override
    public void exportExcel(HttpServletResponse response) {
        // 工作溥
        ExcelSheet<Traveler> sheet = new ExcelSheet<>();
        sheet.setSheetName(ExcelConstants.EXCEL_TEMPLATE_NAME);
        sheet.setPojoClass(Traveler.class);

        List<Traveler> travelers = metaData();
        // 处理图片信息
        String imgPath = imgConfig.getImagePath() + File.separator + imgConfig.getImageName();
        List<Traveler> imgList = travelers.stream().peek(e -> {
            e.setImg(imgPath);
            e.setFile(new File(imgPath));
        }).collect(Collectors.toList());
        sheet.setMateList(imgList);
//        sheet.setMateList(travelers);
        // 导出所有信息
        ExcelUtils.downLoadExcel(ExcelConstants.EXCEL_TEMPLATE_NAME, sheet, response);
        // 自定义导出列
//        sheet.setIncludeColumnFiledNames(getColumnFiledNames());
//        ExcelUtils.exportIncludeColumn(ExcelConstants.EXCEL_TEMPLATE_NAME, sheet, response);
    }

    @Override
    public void exportTemplate(HttpServletResponse response) {
        exportLegionTemplate(response);
//        // 工作溥
//        ExcelSheet<Traveler> sheet = new ExcelSheet<>();
//        sheet.setSheetName(ExcelConstants.EXCEL_TEMPLATE_NAME);
//        sheet.setPojoClass(Traveler.class);
//        sheet.setMateList(example());
//        // 模板不包含文件字段，需排除
//        sheet.setExcludeColumnFiledNames(getColumnFiledNames());
//        // 设置下拉框自定义列表
//        Map<String, String[]> dropdownBoxList = new HashMap<>();
//        dropdownBoxList.put("name", new String[]{"独霸一方", "黄金时代"});
//        ExcelUtils.exportTemplate(
//                ExcelConstants.EXCEL_TEMPLATE_NAME,
//                Instructions.doc(getTemplateDoc(ExcelConstants.TRAVELER_TEMPLATE_DOC_KEY)),
//                dropdownBoxList, sheet, response);
    }

    public void exportLegionTemplate(HttpServletResponse response) {
        // 工作溥
        ExcelSheet<Legion> sheet = new ExcelSheet<>();
        sheet.setSheetName(ExcelConstants.EXCEL_TEMPLATE_NAME);
        sheet.setPojoClass(Legion.class);
        sheet.setMateList(legionExample());
        sheet.setExcludeColumnFiledNames(scanIgnoreExportColumnName(Legion.class));
        Map<String, String[]> dropdownBoxList = new HashMap<>();
        dropdownBoxList.put("campCode", new String[]{"超级兵团", "黄金时代"});
        sheet.setDropdownBoxList(dropdownBoxList);
        sheet.setConverter(new CustomConverter("camp", typeData()));
        ExcelUtils.exportTemplate(
                ExcelConstants.EXCEL_TEMPLATE_NAME,
                Instructions.doc(getTemplateDoc(ExcelConstants.TRAVELER_TEMPLATE_DOC_KEY)),
                sheet, response);
    }

    @Override
    public void exportDayAxis(HttpServletResponse response, Date startTime, Date endTime) {
        // 以时段内的日期轴作为表头
        ZonedDateTime startDay = startTime.toInstant().atZone(ZoneId.of("Asia/Shanghai"))
                .toLocalDate().atStartOfDay().atZone(ZoneId.of("Asia/Shanghai"));
        ZonedDateTime endDay = endTime.toInstant().atZone(ZoneId.of("Asia/Shanghai"))
                .toLocalDate().atStartOfDay().atZone(ZoneId.of("Asia/Shanghai"));
        long dayNum = startDay.until(endDay, ChronoUnit.DAYS);
        // 工作溥
        DynamicSheet sheet = new DynamicSheet();
        sheet.setSheetName(ExcelConstants.EXCEL_TEMPLATE_NAME);
        sheet.setDynamicHead(multiDynamicHead(startDay, dayNum));
        sheet.setList(multiDynamicData(dayNum));
        // 导出
        ExcelUtils.exportDynamicColumn(ExcelConstants.EXCEL_TEMPLATE_NAME, sheet, response);
    }

    @Override
    public void variableTemplate(HttpServletResponse response) {
        // 工作溥
        DynamicSheet sheet = new DynamicSheet();
        List<List<String>> head = dynamicHead();
        sheet.setSheetName(ExcelConstants.EXCEL_TEMPLATE_NAME);
        sheet.setDynamicHead(head);
        sheet.setList(dynamicData(head.size()));
        ExcelUtils.exportDynamicTemplate(ExcelConstants.EXCEL_TEMPLATE_NAME,
                Instructions.doc(getTemplateDoc(ExcelConstants.ROOM_TEMPLATE_DOC_KEY)),
                sheet, response);
    }

    @Override
    public void shareFillExport(HttpServletResponse response) {
        SingleTemplate<Traveler> sheet = new SingleTemplate<>();
        sheet.setSheetName(ExcelConstants.TRAVELER_INFO_NAME);
        Map<String, Object> map = MapUtils.newHashMap();
        map.put("nationality", "中国");
        map.put("startDate", "2020-5-1");
        map.put("endDate", "2020-12-1");
        map.put("nowTime", new Date());
        map.put("totalAssets", 269870);
        sheet.setMetaData(map);
        sheet.setMetaList(metaData());
        List<SingleTemplate<Traveler>> templateSheets = Collections.singletonList(sheet);
        ExcelUtils.shareFillingExcel(
                getExcelTemplate(ExcelConstants.EXCEL_TEMPLATE_PATH, ExcelConstants.TRAVELER_INFO_TEMPLATE),
                ExcelConstants.TRAVELER_INFO_NAME, templateSheets, response);
    }

    @Override
    public void mergeFillExport(HttpServletResponse response) {
        GroupTemplate<Map<String, Object>> sheet = new GroupTemplate<>();
        sheet.setSheetName(ExcelConstants.TRAVELER_ASSET_NAME);
        TravelerAssetTemplate sheetBody = new TravelerAssetTemplate(this::bodyDataSource);
        // 全局填充
        Map<String, Object> map = MapUtils.newHashMap();
        // 如果使用经纬度，请确保格式为（经度,纬度）；目前的天气是珠海
        map.put("weather", getCityWeather("113.57689,22.27164"));
        map.put("tNationalityTotal", 987566);
        map.put("lNationalityTotal", 987422);
        map.put("tAssetTotal", 63588);
        map.put("lAssetTotal", 74655);
        sheet.setMetaData(map);
        // 行组填充数据
        List<ListBodyStyleMap<Map<String, Object>>> listBody = ListUtils.newArrayList();
        // 国籍组
        ArrayList<ItemSubject> nSubjects = sheetBody.getItemSubjects(TravelerAssetTemplate.NATIONALITY_GROUP_NAME);
        List<Map<String, Object>> nList = sheetBody.bodyFromVerticalGroup(nSubjects);
        listBody.add(new ListBodyStyleMap<>(TravelerAssetTemplate.NATIONALITY_GROUP_NAME, nList, 1));
        // 阶级组
        ArrayList<ItemSubject> aSubjects = sheetBody.getItemSubjects(TravelerAssetTemplate.ASSET_GROUP_NAME);
        List<Map<String, Object>> aList = sheetBody.bodyFromVerticalGroup(aSubjects);
        listBody.add(new ListBodyStyleMap<>(TravelerAssetTemplate.ASSET_GROUP_NAME, aList, 2));
        sheet.setListMap(listBody);
        ExcelUtils.mergeFillingExcel(
                getExcelTemplate(ExcelConstants.EXCEL_TEMPLATE_PATH, ExcelConstants.TRAVELER_INFO_TEMPLATE),
                ExcelConstants.TRAVELER_INFO_NAME, sheet, response);
    }

    @Override
    public void syntheticFillExport(HttpServletResponse response) {
        GroupTemplate<Map<String, Object>> sheet = new GroupTemplate<>();
        sheet.setSheetName("合成式");
        TravelerAssetTemplate sheetBody = new TravelerAssetTemplate(this::bodyDataSource);
        sheetBody.initialIndex(5);
        // 全局填充
        Map<String, Object> map = MapUtils.newHashMap();
//        map.put("weather", getCityWeather("113.57689,22.27164"));
        sheet.setMetaData(map);
        // 合计数据
        Map<String, Object> totalMap = MapUtils.newHashMap();
        totalMap.put("nationalitytAssets", 987566);
        totalMap.put("assettAssets", 63588);
        sheetBody.setTotalRowData(totalMap);
        // 复合组
        GroupSubject nationalitySubject = sheetBody.getGroupSubject(TravelerAssetTemplate.NATIONALITY_GROUP_NAME, "国籍组");
        List<Map<String, Object>> groupData = sheetBody.bodyFromVerticalGroup(nationalitySubject);
        GroupSubject assetSubject = sheetBody.getGroupSubject(TravelerAssetTemplate.ASSET_GROUP_NAME, "阶级组");
        List<Map<String, Object>> assetData = sheetBody.bodyFromVerticalGroup(assetSubject);
        groupData.addAll(assetData);
        // 复合组样式处理器
        List<Integer> titleRowIndex = sheetBody.getTitleRowIndex();
        HashMap<Integer, String> titleRowCol = new HashMap<>();
        for (Integer rowIndex : titleRowIndex) {
            titleRowCol.put(rowIndex, "0");
        }
        List<Integer> totalRowIndex = sheetBody.getTotalRowIndex();
        HashMap<Integer, String> totalRowCol = new HashMap<>();
        for (Integer rowIndex : totalRowIndex) {
            totalRowCol.put(rowIndex, "-1");
        }
        DefaultFlatGroupStyle flatGroupStyleHandler = DefaultFlatGroupStyle.build(titleRowCol, totalRowCol);
        ListBodyStyleMap<Map<String, Object>> flatGroupStyleMap = new ListBodyStyleMap<>(TravelerAssetTemplate.MULTIPLE_GROUP_NAME, groupData, 1);
        flatGroupStyleMap.setHandlers(flatGroupStyleHandler);
        sheet.addListMap(flatGroupStyleMap);
        // 特殊组数据（测试不同组循环的样式是否正常）
        ArrayList<ItemSubject> aSubjects = sheetBody.getItemSubjects(TravelerAssetTemplate.NATIONALITY_GROUP_NAME);
        List<Map<String, Object>> aList = sheetBody.bodyFromVerticalGroup(aSubjects);
        // 特殊组样式处理器
        HashMap<Integer, String> universalRowCol = new HashMap<>();
        universalRowCol.put(17, "-1");
        universalRowCol.put(18, "-1");
        SpecialGroupTemplate universalGroupStyle = new SpecialGroupTemplate();
        HashMap<Integer, String> universalFormulaRowCol = new HashMap<>();
        universalFormulaRowCol.put(17, "4,8");
        universalFormulaRowCol.put(18, "4,8");
        universalGroupStyle.setFormulaRowColMap(universalFormulaRowCol);
        universalGroupStyle.setStyleRowColMap(universalRowCol);
        ListBodyStyleMap<Map<String, Object>> universalGroupStyleMap = new ListBodyStyleMap<>(TravelerAssetTemplate.SPECIAL_GROUP_NAME, aList, 2);
        universalGroupStyleMap.setHandlers(universalGroupStyle);
        sheet.addListMap(universalGroupStyleMap);
        ExcelUtils.mergeFillingExcel(
                getExcelTemplate(ExcelConstants.EXCEL_TEMPLATE_PATH, ExcelConstants.TRAVELER_INFO_TEMPLATE),
                ExcelConstants.TRAVELER_INFO_NAME, sheet, response);
    }

    /**
     * 导入Excel
     * {@link ExcelReaderBuilder#doReadAll()} 读取所有sheet
     * {@link ExcelReaderBuilder#registerConverter(Converter)}  注册全局转换器， 所有java为和excel均为指定类型的都会用这个转换器
     * {@link ExcelReaderBuilder#headRowNumber(Integer)}        设置多行头，默认行头数量为1
     */
    @Override
    public void importExcel(MultipartFile file) throws ExcelImportException {
        try {
            // 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器
            // 需要指定读用哪个class去读，未指定工作薄则默认读取第一个sheet文件流会自动关闭
            EasyExcel.read(file.getInputStream(), Traveler.class, new SimpleImportListener(workDao, transactionTemplate))
                    .headRowNumber(2).sheet(ExcelConstants.EXCEL_TEMPLATE_NAME).doRead();
//            EasyExcel.read(file.getInputStream(), Legion.class, new LegionImportListener())
//                    .registerConverter(new CustomConverter("camp", typeData())).headRowNumber(2).sheet(ExcelConstants.EXCEL_TEMPLATE_NAME).doRead();
        } catch (IOException e) {
            throw new ExcelImportException("无效的导入文件!");
        }
    }

    @Override
    public void variableImport(MultipartFile file) throws ExcelImportException {
        try {
            EasyExcel.read(file.getInputStream(), new NoModelImportListener(transactionTemplate))
                    .sheet(ExcelConstants.EXCEL_TEMPLATE_NAME).doRead();
        } catch (IOException e) {
            throw new ExcelImportException("无效的导入文件!");
        }
    }

    @Override
    public void importLocalExcel() throws ExcelImportException {
        String pathName = "C:\\Users\\mochu\\Desktop\\旅行者.xlsx";
        ExcelReader excelReader = EasyExcel.read(pathName, Traveler.class, new SimpleImportListener(workDao, transactionTemplate)).build();
        // 单独构建一个sheet用于读取，这里可以指定名字或者no
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        // 读取，可用同时传入多个sheet
        excelReader.read(readSheet).finish();
    }

    private List<Traveler> metaData() {
        List<Traveler> travelerList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Traveler data = new Traveler();
            data.setCode(String.valueOf(102 + i));
            data.setName("测试数据&" + i);
            data.setSex(1);
            data.setAssets(BigDecimal.valueOf(10000 * i));
            data.setPhone("1304568903" + i);
            data.setBirthday(new Date());
            data.setProcess(0.30);
            travelerList.add(data);
        }
        return travelerList;
    }

    private List<Legion> legionData() {
        List<Legion> legionList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Legion data = new Legion();
            data.setName("测试数据&" + i);
            data.setCampCode("102");
            legionList.add(data);
        }
        return legionList;
    }

    private List<Traveler> example() {
        List<Traveler> exampleList = new ArrayList<>();
        Traveler data = new Traveler();
        data.setCode("102");
        data.setName("示例数据");
        data.setSex(1);
        data.setAssets(BigDecimal.TEN);
        data.setPhone("15068943789");
        data.setBirthday(new Date());
        data.setFamily("千雪&段无味&...");
        data.setProcess(0.50);
        exampleList.add(data);
        return exampleList;
    }

    private List<Legion> legionExample() {
        List<Legion> exampleList = new ArrayList<>();
        Legion data = new Legion();
        data.setName("示例数据");
        data.setCampCode("102");
        exampleList.add(data);
        return exampleList;
    }

    private List<CustomType> typeData() {
        List<CustomType> typeList = new ArrayList<>();
        CustomType ct = new CustomType();
        ct.setCode("102");
        ct.setName("超级兵团");
        typeList.add(ct);
        return typeList;
    }

    /**
     * 一行动态头
     */
    private List<List<String>> dynamicHead() {
        List<List<String>> heads = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("房间号");
        heads.add(head0);
        // 从数据库查询的房间标签（动态数据）
        List<String> head1 = new ArrayList<>();
        head1.add("海景");
        heads.add(head1);
        List<String> head2 = new ArrayList<>();
        head2.add("南向");
        heads.add(head2);
        List<String> head3 = new ArrayList<>();
        head3.add("安静");
        heads.add(head3);
        return heads;
    }

    private List<List<Object>> dynamicData(Integer headNum) {
        List<List<Object>> dataList = new ArrayList<>();
        List<Object> data = new ArrayList<>();
        data.add("A101");
        for (int i = 1; i < headNum; i++) {
            data.add((i & 1) == 1 ? 0 : 1);
        }
        dataList.add(data);
        return dataList;
    }

    /**
     * 多行动态头
     */
    private List<List<String>> multiDynamicHead(ZonedDateTime startDay, Long dayNum) {
        List<List<String>> heads = new ArrayList<>();
        String initialGrid = "-";
        // 表头有两行，因此纵向合并两个单元格(左上角)
        heads.add(Arrays.asList(initialGrid, initialGrid));
        for (int i = 0; i < (dayNum + 1); i++) {
            ZonedDateTime day = startDay.plusDays(i);
            // 日期头
            String dayAxis = MessageFormat.format("{0}-{1}", day.getMonthValue(), day.getDayOfMonth());
            // 横向合并三个单元格(日期头)，并生成子表头
            heads.add(Arrays.asList(dayAxis, "可售"));
            heads.add(Arrays.asList(dayAxis, "已售"));
            heads.add(Arrays.asList(dayAxis, "库存"));
        }
        return heads;
    }

    private List<List<Object>> multiDynamicData(Long dayNum) {
        List<List<Object>> dataList = new ArrayList<>();
        int size = 10;
        // 每个房型作为一条记录
        for (int i = 1; i <= size; i++) {
            List<Object> record = new ArrayList<>(Collections.singletonList("房型-" + i));
            // 每个房型当天的库存
            for (int a = 0; a < (dayNum + 1); a++) {
                record.add(10);
                record.add(0);
                record.add(10);
            }
            dataList.add(record);
        }
        return dataList;
    }

    private Map<String, Object> bodyDataSource(TravelerQuery query) {
        HashMap<String, Map<String, Object>> source = MapUtils.newHashMap();
        Map<String, Object> america = MapUtils.newHashMap();
        america.put("name", "美联储");
        america.put("assets", BigDecimal.valueOf(8596));
        america.put("process", 999.99);
        source.put("america", america);
        Map<String, Object> china = MapUtils.newHashMap();
        china.put("name", "央行");
        china.put("assets", BigDecimal.valueOf(5869));
        china.put("process", 999.99);
        source.put("china", china);
        Map<String, Object> britain = MapUtils.newHashMap();
        britain.put("name", "英格兰银行");
        britain.put("assets", BigDecimal.valueOf(6895));
        britain.put("process", 999.99);
        source.put("britain", britain);
        Map<String, Object> france = MapUtils.newHashMap();
        france.put("name", "法国银行");
        france.put("assets", BigDecimal.valueOf(6689));
        france.put("process", 999.99);
        source.put("france", france);
        Map<String, Object> richPeople = MapUtils.newHashMap();
        richPeople.put("subject", "上层阶级");
        richPeople.put("name", "富豪");
        richPeople.put("assets", BigDecimal.valueOf(120));
        richPeople.put("process", 666.99);
        source.put("richPeople", richPeople);
        Map<String, Object> civilian = MapUtils.newHashMap();
        civilian.put("subject", "中产阶级");
        civilian.put("name", "平民");
        civilian.put("assets", BigDecimal.valueOf(56));
        civilian.put("process", 333.99);
        source.put("civilian", civilian);
        Map<String, Object> poorPeople = MapUtils.newHashMap();
        poorPeople.put("subject", "小资阶级");
        poorPeople.put("name", "贫民");
        poorPeople.put("assets", BigDecimal.valueOf(6));
        poorPeople.put("process", 100.99);
        source.put("poorPeople", poorPeople);
        String param1 = query.getVerticalAxis();
        String param2 = query.getHorizontalAxis();
        logger.info(MessageFormat.format("条件1{0}和条件2{1}-{2}", param1, param2, source.get(param1)));
        return source.get(param1);
    }

    private List<String> getTemplateDoc(String jsonDoc) {
        List<String> docs = new ArrayList<>();
        try {
            ClassPathResource resource = new ClassPathResource(ExcelConstants.DOC_JSON_PATH);
            ObjectMapper objectMapper = new ObjectMapper();
            InputStream in = resource.getInputStream();
            JsonNode jsonNode = objectMapper.readTree(in).get(jsonDoc);
            Iterator<JsonNode> arrayNode = jsonNode.elements();
            while (arrayNode.hasNext()) {
                String docItem = arrayNode.next().textValue();
                docs.add(docItem);
            }
        } catch (IOException e) {
            logger.error("说明书的JSON文件未找到，或读取失败！");
        }
        return docs;
    }

    /**
     * 扫描导出时应该忽略的字段
     */
    private <T> Set<String> scanIgnoreExportColumnName(Class<T> pojoClass) {
        Field[] declaredFields = pojoClass.getDeclaredFields();
        Set<String> ignoreColumnNames = new HashSet<>(declaredFields.length);
        for (Field declaredField : declaredFields) {
            boolean isIgnore = declaredField.isAnnotationPresent(ExcelExportIgnore.class);
            if (!isIgnore) {
                continue;
            }
            ignoreColumnNames.add(declaredField.getName());
        }
        return ignoreColumnNames;
    }

    /**
     * 获取Excel模板文件的输入流
     *
     * @param tempPath     模板文件所在的路径
     * @param templateName 模板文件的名称
     */
    private InputStream getExcelTemplate(String tempPath, String templateName) {
        String templateFile = tempPath + File.separator + templateName + ExcelTypeEnum.XLSX.getValue();
        try {
            ClassPathResource resource = new ClassPathResource(templateFile);
            return resource.getInputStream();
        } catch (IOException e) {
            throw new CheckedException("报表模板加载失败！");
        }
    }

    /**
     * 获取城市的实时天气
     *
     * @param location 需要查询地区的LocationID或经纬度(经度,纬度){@code location=116.41,39.92}
     */
    private String getCityWeather(String location) {
        String weather = "";
        String weatherURL = null;
        try {
            weatherURL = getWeatherURL(WeatherKey.WEATHER);
        } catch (InvalidURL e) {
            return weather;
        }
        // 如果认证key失效，请替换成自己的key；目前免费订阅的认证key足以支持大部分功能
        JsonNode weatherNode = queryWeatherURL(weatherURL, location, "631ad11a12a54c08bf59a7e962b85a73");
        JsonNode now = weatherNode.get("now");
        weather = MessageFormat.format("{0},{1}℃", now.get("text").asText(), now.get("temp").asInt());
        return weather;
    }

    private String getWeatherURL(String urlKey) throws InvalidURL {
        try {
            ClassPathResource resource = new ClassPathResource(ExcelConstants.WEATHER_URL);
            InputStream in = resource.getInputStream();
            return objectMapper.readTree(in).get(urlKey).textValue();
        } catch (IOException e) {
            logger.error("天气地址未找到！");
        }
        throw new InvalidURL("天气地址未找到");
    }

    private JsonNode queryWeatherURL(String url, String cityId, String key) {
        String queryURL = MessageFormat.format("{0}?location={1}&key={2}", url, cityId, key);
        return initiateRequest(queryURL);
    }

    private JsonNode initiateRequest(String request) {
        JsonNode jsonNode = null;
        try {
            URL queryURL = new URL(request);
            // 打开连接
            HttpURLConnection connection = (HttpURLConnection) queryURL.openConnection();
            connection.setRequestProperty("Accept-Encoding", "gzip");
            connection.setRequestProperty("Content-Type", "application/json;charset=UTF-8");
            // 设置请求方法为GET
            connection.setRequestMethod("GET");
            // 获取响应代码
            int responseCode = connection.getResponseCode();
            if (responseCode == HttpURLConnection.HTTP_OK) {
                // 检查响应是否使用Gzip压缩
                String contentEncoding = connection.getHeaderField("Content-Encoding");
                boolean isGzipped = "gzip".equalsIgnoreCase(contentEncoding);
                // 读取响应内容
                BufferedReader reader;
                if (isGzipped) {
                    reader = new BufferedReader(new InputStreamReader(new GZIPInputStream(connection.getInputStream())));
                } else {
                    reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
                }
                String line;
                StringBuilder response = new StringBuilder();
                while ((line = reader.readLine()) != null) {
                    response.append(line);
                }
                reader.close();
                // 输出响应内容
                jsonNode = objectMapper.readTree(response.toString());
            } else {
                logger.error("天气获取失败！");
            }
            // 断开连接
            connection.disconnect();
        } catch (Exception e) {
            logger.error("天气获取失败！");
        }
        return jsonNode;
    }

    private Set<String> getColumnFiledNames() {
        Set<String> columnFiledNames = new HashSet<>();
        columnFiledNames.add("img");
        columnFiledNames.add("file");
        return columnFiledNames;
    }
}
